/*
 * @Description: 方便往数据库里录入数据
 * @Date: 2023-06-09 15:05:02
 * @LastEditTime: 2024-03-13 18:05:00
 * @FilePath: \node-backend-services\router_handler\tool.js
 */

const { v4: uuidv4 } = require('uuid'); ///nodejs的版本太低会报错，尽量16以上
const fs = require("fs");


const db = require('../db/index');
var iconv = require('iconv-lite');

let xlsx = require('node-xlsx');
const { send } = require('process');
const { create } = require('domain');

//导入文件接口数据存入数据库(含有children)
exports.importFileData = (req, res) => {
  fs.readFile('./upload/jsonData/data.json', 'utf-8', (err, data) => {
    if (err) return res.cc(err)
    // res.send({ data: "信息插入成功", data:JSON.parse(data) })
    let getAllChildren = (items) => {
      return items.reduce((acc, item) => {
        const { children = [], ...rest } = item;
        return [...acc, rest, ...getAllChildren(children)];
      }, []);
    }
    let formatData = getAllChildren(JSON.parse(data))
    const sql = `insert ignore into cate_data set ?`    //用来排除数据重复的。
    formatData.forEach(element => {
      db.query(sql, element, (err, results) => {
        try {
          if (err) throw err
        } catch (err) {
          console.log(err);
        }
      })
    });
  })
}


//导入excel文件，写入数据库
exports.importQuestion = (req, res) => {
  let sheets = xlsx.parse('./upload/excelQuestion/123.xlsx');
  let arr = []; // 全部表中的数据
  sheets.forEach((sheet) => {
    for (let i = 1; i < sheet['data'].length; i++) {
      //excel第一行是是表头，所以从1开始循环
      let row = sheet['data'][i]; // 获取行数据
      if (row && row.length > 0) {
        // moment处理 ISO 8601格式的时间,
        arr.push({
          id:uuidv4(),
          ques_name: iconv.decode(row[0] , 'gbk') ,     // row[0]对应表格里A列
          ques_answer:iconv.decode(row[1] , 'gbk') , // row[1]对应表格里B列  如果出现乱码可以使用iconv-lite进行转码
          ques_type: iconv.decode(row[2] , 'gbk') ,    // row[2]对应表格里C列
          create_time: new Date(),
        });
      }
    }
  });
  const sql = `insert into ques_data set ?`
  arr.forEach(element => {
    db.query(sql, element, (err, results) => {
      if(err) return res.cc(err)
      res.send({
        meta: {
          status: 200,
          msg: '读取xlsx成功！',
        },
      })
    })
  });
 
}

